﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Common.Utilities;
using System.Data.SqlClient;
using System.Data;

namespace Workflows.Components.ETL
{
    /// <summary>
    /// 
    /// </summary>
    public class WosLinker
    {
        const string connStr = "Server=DLS14SQL64;Database=WebOfScience;Integrated Security=SSPI;";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="authors"></param>
        /// <returns></returns>
        public static List<string> SplitAuthors(string authors)
        {
            Regex etalRegex=new Regex("\\s+et[\\s\\.]+al[\\s;:,\\.]*", RegexOptions.IgnoreCase);
            authors = etalRegex.Replace(authors, "");
            List<string> authorList = new List<string>();
            string[] authorNames = authors.Split(new char[] { ',', '\'' });
            foreach (string author in authorNames)
            {
                string encodedName = author.Trim();
                if (encodedName.ToLower().IndexOf(" and ") >= 0)
                {
                    int pos = encodedName.ToLower().IndexOf(" and ");
                    encodedName = encodedName.Substring(0, pos) + encodedName.Substring(pos + " and ".Length);
                }
                else if (encodedName.ToLower().StartsWith("and "))
                    encodedName = encodedName.Substring(" and".Length);
                else if (encodedName.ToLower().EndsWith(" and"))
                    encodedName = encodedName.Substring(0, encodedName.Length - " and".Length);
                if (encodedName.Length == 2 && (encodedName[0] == '.' || encodedName[1] == '.'))
                    encodedName = "";
                if (encodedName.Trim().Length >= 2)
                {
                    authorList.Add(encodedName);
                }
            }
            return authorList;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageText"></param>
        /// <returns></returns>
        public static Range<int> SplitPages(string pageText)
        {
            Range<int> pages = new Range<int>();
            pages.FromValue = 0;
            pages.ToValue = 0;
            if (!string.IsNullOrEmpty(pageText))
            {
                Regex pageRegex = new Regex("(\\d+)");
                Match pageMatch = pageRegex.Match(pageText);
                while (pageMatch.Success)
                {
                    int pageNum = int.Parse(pageMatch.Groups[1].Value);
                    if (pages.FromValue == 0)
                    {
                        pages.FromValue = pageNum;
                    }
                    else if (pages.ToValue == 0)
                    {
                        pages.ToValue = pageNum;
                    }
                    else
                    {
                        pages.FromValue = pages.ToValue;
                        pages.ToValue = pageNum;
                    }
                    pageMatch = pageMatch.NextMatch();
                }
            }

            return pages;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="authorNames"></param>
        /// <param name="journalName"></param>
        /// <param name="volume"></param>
        /// <param name="issue"></param>
        /// <param name="articleTitle"></param>
        /// <param name="pubYear"></param>
        /// <param name="pages"></param>
        /// <returns></returns>
        public static WosItem FindLinkedWosItem(List<string> authorNames, string journalName,
            string volume, string issue, string articleTitle, int pubYear,
            Range<int> pages)
        {
            int journalID = WosLinker.FindWosJournal(journalName);
            string journalISSN = WosLinker.GetJournalISSN(journalID);
            int[] issueIDs = WosLinker.FindWosIssue(journalISSN, volume, issue, pubYear);
            WosItem item = null;
            double lastScore = 0;
            if (issueIDs != null && issueIDs.Length > 0)
            {
                foreach (int issueID in issueIDs)
                {
                    List<int> itemIDs = WosLinker.FindWosSourceItem(issueID, authorNames, pages);
                    if (itemIDs != null && itemIDs.Count > 0)
                    {
                        foreach (int itemID in itemIDs)
                        {
                            WosItem wosItem = WosLinker.GetWosSourceItem(itemID);
                            double titleScore = GetTitleScore(articleTitle, wosItem.Title);
                            double authorScore = GetAuthorScore(authorNames, wosItem.Authors);
                            double score = titleScore + authorScore;
                            if (score >= 0.7 && score > lastScore)
                            {
                                lastScore = score;
                                item = wosItem;
                            }
                        }
                    }
                }
            }

            if (lastScore >= 0.7)
                return item;
            else
                return null;
        }

        private static double GetAuthorScore(List<string> authorList, string authors)
        {
            authors = authors.ToLower();
            int totalMatch = 0;
            int maxMatch = 0;
            foreach(string author in authorList)
                maxMatch += author.Length;
            foreach(string author in authorList)
            {
                if(authors.Contains(author.ToLower()))
                    totalMatch += author.Length;
            }
            return (double) totalMatch/maxMatch;
        }

        private static double GetTitleScore(string title1, string title2)
        {
            if (!string.IsNullOrEmpty(title1) && !string.IsNullOrEmpty(title2))
            {
                int commonLen = StringUtil.LongestCommonSubsequence(title1, title2);
                return (double) commonLen/Math.Max(title1.Length, title2.Length);
            }
            else
                return 0;
        }

        #region journal
        private static int FindWosJournal(string journalName)
        {
            int journalIDFound = 0;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;

                Regex journalNameRegex=new Regex("[a-zA-Z]+");
                string jName = string.Empty;
                Match jMatch = journalNameRegex.Match(journalName);
                while(jMatch.Success)
                {
                    if (jName.Length > 0)
                        jName += " ";
                    jName += jMatch.Value;
                    jMatch = jMatch.NextMatch();
                }
                journalName = jName;

                string q = WosLinker.BuildFreeTextSearchQuery(WosLinker.GetEncodedWords(journalName));
                cmd.CommandText = string.Format("select j.JournalID, j.Title from Journal j where Contains(j.Title, '{0}')", q);
                double maxScore = 0;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["JournalID"] != DBNull.Value && reader["Title"] != DBNull.Value)
                    {
                        int journalID = (int)reader["JournalID"];
                        string title = (string)reader["Title"];
                        double matchScore = StringUtil.LongestCommonSubsequence(title, journalName);
                        matchScore = matchScore / Math.Max(title.Length, journalName.Length);
                        if (matchScore > maxScore)
                        {
                            maxScore = matchScore;
                            journalIDFound = journalID;
                        }
                    }
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to find journal: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return journalIDFound;
        }

        private static string GetJournalISSN(int journalID)
        {
            string issn = string.Empty;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select ISSN from Journal where JournalID=" + journalID;
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    issn = reader.GetString(0);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to get journal ISSN: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return issn;
        }

        private static Dictionary<string, double> GetEncodedWords(string name)
        {
            name = name.ToLower();
            string[] words = name.Split(new char[] { ' ', '-', '.', ',' });
            Dictionary<string, double> encodedWords = new Dictionary<string, double>();
            double weight = 1.0;
            foreach (string word in words)
            {
                if (StringUtil.IsAlphaNumeric(word))
                {
                    if (word != "of" && word != "and" && word != "the" && word != "a" && word != "an" &&
                        word != "for" && word != "at")
                    {
                        weight -= encodedWords.Count * 1.0 / words.Length;
                        string workTrim = word;
                        if (word.Length > 3)
                            workTrim = word.Substring(0, 3);
                        if (!encodedWords.ContainsKey(workTrim))
                            encodedWords.Add(workTrim, weight);
                    }
                }
            }
            return encodedWords;
        }

        private static string BuildFreeTextSearchQuery(Dictionary<string, double> encodedWords)
        {
            string s1 = string.Empty;
            string s2 = string.Empty;
            foreach (string word in encodedWords.Keys)
            {
                if (encodedWords[word] >= 0.5)
                {
                    if (s1.Length > 0)
                        s1 += " AND ";
                    s1 += "\"" + word + "*\"";
                }
                else
                {
                    if (s2.Length > 0)
                        s2 += " OR ";
                    s2 += "\"" + word + "*\"";
                }
            }
            if (s1.Length > 0)
            {
                if (s2.Length > 0)
                    return "(" + s1 + ") OR (" + s2 + ")";
                else
                    return s1;
            }
            else if (s2.Length > 0)
                return s2;
            else
                return "";
        }
        #endregion

        #region issue
        private static int[] FindWosIssue(string issn, string volume, string issue, int pubYear)
        {
            List<int> issueIDs = new List<int>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"select i.SourceIssueID 
from 
	SourceIssue i 
where 
	i.ISSN='{0}'
	and i.Volume like '%{1}%'
	and i.Issue like '%{2}%'
	and datepart(year,i.DLPubDate)={3}";
                cmd.CommandText = string.Format(sql, issn, volume ?? "", issue ?? "", pubYear);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    issueIDs.Add(reader.GetInt32(0));
                }
                reader.Close();

                if(issueIDs.Count==0 && !string.IsNullOrEmpty(issue))
                {
                    issueIDs.AddRange(WosLinker.FindWosIssue(issn, volume, null, pubYear));
                }
                if(issueIDs.Count==0 && !string.IsNullOrEmpty(volume))
                {
                    issueIDs.AddRange(WosLinker.FindWosIssue(issn, null, null, pubYear));
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to find issue: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return issueIDs.ToArray();
        }
        #endregion

        #region item
        private static List<int> FindWosSourceItem(int issueID, List<string> authorNames, Range<int> pages)
        {
            List<int> itemIDs = new List<int>();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"select distinct 
	s.SourceItemId
from 
	dbo.Author a 
	inner join dbo.SourceItemAuthor sa on a.AuthorId=sa.AuthorId
	inner join dbo.SourceItem s on sa.SourceItemId=s.SourceItemId
where 
	s.SourceIssueId={0}
	and ({1});";
                string inAuthorNames = WosLinker.BuildAuthorLookupQuery("a.AuthorName", authorNames);
                cmd.CommandText = string.Format(sql, issueID, inAuthorNames);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    int itemID = reader.GetInt32(0);
                    itemIDs.Add(itemID);
                }
                reader.Close();

                if (itemIDs.Count == 0 && pages != null && pages.FromValue > 0 && pages.ToValue >= pages.FromValue)
                {
                    sql = @"select distinct 
	s.SourceItemId
from 
	dbo.Author a 
	inner join dbo.SourceItemAuthor sa on a.AuthorId=sa.AuthorId
	inner join dbo.SourceItem s on sa.SourceItemId=s.SourceItemId
where 
	s.SourceIssueId={0}
	and {1} and {2};";
                    string fromPage = string.Format("BeginningPage like '%{0}%'", pages.FromValue);
                    string toPage = string.Format("EndingPage like '%{0}%'", pages.ToValue);
                    cmd.CommandText = string.Format(sql, issueID, fromPage, toPage);
                    reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        int itemID = reader.GetInt32(0);
                        itemIDs.Add(itemID);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to find issue: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }

            return itemIDs;
        }

        private static string BuildAuthorLookupQuery(string nameField, List<string> authorNames)
        {
            string sql = string.Empty;
            foreach (string authorName in authorNames)
            {
                if (authorName.Trim().Length == 0)
                    continue;
                string q = string.Empty;
                if (authorName.IndexOf(",") > 0)
                {
                    string lastName = authorName.Substring(0, authorName.IndexOf(",")).Trim();
                    string firstName = authorName.Substring(authorName.IndexOf(",") + 1).Trim();
                    if (lastName.Length > 0)
                    {
                        q = string.Format("Contains({0},'\"{1}\"')", nameField, lastName);
                    }
                }
                else
                {
                    string[] parts = authorName.Split(new char[] { ' ' });
                    if (parts.Length < 2)
                    {
                        q = string.Format("Contains({0},'\"{1}\"')", nameField, authorName.Trim());
                    }
                    else if (parts.Length == 2)
                    {
                        q = string.Format("Contains({0},'\"{1}\"')", nameField, parts[1]);
                    }
                    else
                    {
                        q = string.Format("Contains({0},'\"{1}\"')", nameField, parts[parts.Length - 1]);
                    }
                }
                if (q.Length > 0)
                {
                    if (sql.Length > 0)
                    {
                        sql += " OR ";
                    }
                    sql += q;
                }
            }
            return sql;
        }

        private static WosItem GetWosSourceItem(int itemID)
        {
            WosItem wosItem = new WosItem();
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                string sql = @"select distinct 
	j.Title as JournalName,
	i.Volume,
	i.Issue,
	Year(i.DLPubDate) as PubYear,
	s.ArticleTitle as Title,
	s.SourceItemId,
	s.SourceIssueId
from 
	dbo.SourceItem s,
	dbo.SourceIssue i,
	dbo.Journal j
where 
	s.SourceIssueId=i.SourceIssueId
	and i.ISSN=j.ISSN
	and s.SourceItemId={0}";
                cmd.CommandText = string.Format(sql, itemID);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    wosItem.JournalName = reader["JournalName"].ToString();
                    wosItem.Volume = reader["Volume"] == DBNull.Value ? "" : reader["Volume"].ToString();
                    wosItem.Issue = reader["Issue"] == DBNull.Value ? "" : reader["Issue"].ToString();
                    wosItem.Title = reader["Title"] == DBNull.Value ? "" : reader["Title"].ToString();
                    wosItem.PubYear = reader["PubYear"] == DBNull.Value ? 0 : int.Parse(reader["PubYear"].ToString());
                    wosItem.SourceIssueID = int.Parse(reader["SourceIssueID"].ToString());
                    wosItem.SourceItemID = itemID;
                }
                reader.Close();
                if (wosItem.SourceItemID > 0)
                {
                    sql = @"select a.AuthorName
from dbo.Author a 
inner join dbo.SourceItemAuthor sa on a.AuthorId=sa.AuthorId
where sa.SourceItemId={0}";
                    cmd.CommandText = string.Format(sql, itemID);
                    reader = cmd.ExecuteReader();
                    wosItem.Authors = string.Empty;
                    while (reader.Read())
                    {
                        string author = reader.GetString(0);
                        if (wosItem.Authors.Length > 0)
                            wosItem.Authors += ", ";
                        wosItem.Authors += author;
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Unable to get details for source item: " + ex.ToString());
            }
            finally
            {
                conn.Close();
            }
            return wosItem;
        }
        #endregion
    }
}
